SET NOCOUNT ON;
declare @version smallint = 1;
declare @release smallint = 3;
declare @script_name varchar(256)
set @script_name='UtilityDB_V'+convert(varchar(5),@version)+'R'
	+right('00000'+convert(varchar(5),@release),5)+'.sql'

if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=1)
	print 'script '+@script_name+' already undone.';
else if not exists (select 1 from dbo.database_version_information where script_name=@script_name)
	print 'script '+@script_name+' has never been executed, undo can not be executed';
else
begin try
begin tran
/*--Begin Code Here --*/

if exists (select 1 from sys.tables where name='logging' and schema_name(schema_id)='udb')
BEGIN
	drop table udb.logging;
END

if exists (select * from sys.procedures where name='logInsert' and schema_name(schema_id)='udb')
BEGIN
	drop procedure udb.logInsert;
END

if exists (select * from sys.procedures where name='logInsertTableVariable' and schema_name(schema_id)='udb')
BEGIN
	drop procedure udb.logInsertTableVariable;
END

if exists (select * from sys.types where name='logging_table' and schema_name(schema_id)='udb')
BEGIN
	drop type udb.[logging_table];
END

/*--End Code Here --*/
update dbo.database_version_information 
set undone=1
,date_released=getdate()
where
script_name=@script_name;

print 'script Undo_'+@script_name+' succeeded!'
commit tran
end try
begin catch 
	print 'script '+@script_name+' FAILED!ROLLBACK!'
	print error_message();
	print error_number();
	Rollback
end catch